Deskripsi

Manajemen data infeksi dan vaksinasi Covid-19 seluruh negara.

Input

  1. Infected Case

    Sumber: Center for Systems Science and Engineering (CSSE) at Johns Hopkins University (GitHub)

    URL: https://github.com/CSSEGISandData/COVID-19

    Terdiri dari tiga data set:

    • Confirmed
    • Recovered
    • Death
  2. Population dan Kode Negara

    Sumber: Center for Systems Science and Engineering (CSSE) at Johns Hopkins University (GitHub)

    URL: https://github.com/CSSEGISandData/COVID-19

  3. Histori Vaksin

    Sumber: Our World in Data (Github)

    URL: https://github.com/owid/covid-19-data

  4. Vaccine manufacture

    Sumber: Our World in Data (Github)

    URL: https://github.com/owid/covid-19-data

Output

  1. Data historis (total akumulatif harian) kasus infeksi dan vaksinasi
  2. Data terkini dari kasus infeksi dan vaksinasi

Tools

  1. Package

    • base
    • tidyverse (dplyr, readr, lubridate)
  2. Function

    • read_csv
    • select
    • rename
    • arrange
    • group_by
    • mutate
    • summarise
    • left_join
    • pivot_wider
    • pivot_longer

library(tidyverse)
library(lubridate)

Infected Case

Sumber data:

Input:

Output:

infected <- function(path){
  read_csv(path) %>%
  rename_all(make.names) %>%
  pivot_longer(cols = starts_with("X"),
                 names_to = "last_update",
                 values_to = "total") %>%
  mutate(last_update = mdy(sub("X","", last_update))) %>%
  rename(country = Country.Region) %>%
  group_by(country, last_update) %>%
  summarise(total = sum(total)) %>%
  ungroup()
}
path_confirmed <- "../data/time_series_covid19_confirmed_global.csv"
path_recovered <- "../data/time_series_covid19_recovered_global.csv" 
path_death <- "../data/time_series_covid19_deaths_global.csv"

# data downloaded from
# https://github.com/CSSEGISandData/COVID-19/tree/master/csse_covid_19_data/csse_covid_19_time_series

confirmed <- read_csv(path_confirmed)
confirmed
# join all together
infected_case <- infected(path_confirmed) %>% 
  left_join(infected(path_recovered), 
            by = c("country", "last_update")) %>%
  left_join(infected(path_death)
            , by = c("country", "last_update")) %>%
  rename(total_confirmed = total.x,
         total_recovered = total.y,
         total_death     = total) %>%
  mutate(total_active    = total_confirmed - (total_recovered + total_death)) %>%
  mutate(across(starts_with("total"), ~replace_na(., 0)))

infected_case

Populasi dan kode negara

Sumber data:

Input:

Output:

path <- "../data/UID_ISO_FIPS_LookUp_Table.csv"

# data downloaded from
# https://github.com/CSSEGISandData/COVID-19/tree/master/csse_covid_19_data

country <- read_csv(path)
country
country <- country %>%
  filter(UID == code3) %>%
  # provinsi tapi mempunyai kode negara, dianggap sebagai negara
  mutate(Country_Region = ifelse(is.na(Province_State), Country_Region, Province_State)) %>%
  select (iso_code = iso3,
          country = Country_Region,
          latitude = Lat,
          longitude = Long_,
          population = Population)

country

Historis Vaksin

Sumber data:

Input:

Output:

path <- "../data/vaccinations.csv"

# data downloaded from
# https://github.com/owid/covid-19-data/tree/master/public/data/vaccinations

vaccination <- read_csv(path)
vaccination
vaccination <- vaccination %>%
  select(location:total_boosters) %>%
  mutate(across(total_vaccinations:total_boosters, 
                ~na_if(., 0))) %>%
  group_by(location, iso_code) %>%
  arrange(location, iso_code, date) %>%
  fill(total_vaccinations:total_boosters, 
       .direction = "down") %>%
  ungroup() %>%
  mutate(across(total_vaccinations:total_boosters, 
                ~replace_na(., 0))) %>%
  rename(country = location, last_update = date)

vaccination

Vaccine manufacture

Sumber data:

Input/Output:

path <- "../data/locations.csv"

# data downloaded from
# https://github.com/owid/covid-19-data/tree/master/public/data/vaccinations

vaccine_manufacture <- read_csv(path)
vaccine_manufacture

Data Historis Infected Case VS Vaccination

Input:

Ouput:

c19_daily <- infected_case %>%
  left_join(country, by = "country") %>%
  select(iso_code, country, population, last_update,
         starts_with("total")) %>%
  left_join(vaccination, by = c("iso_code", "last_update")) %>%
  group_by(iso_code) %>%
  arrange(iso_code, last_update) %>%
  fill(total_vaccinations:total_boosters, 
       .direction = "down") %>%
  ungroup() %>%
  mutate(across(total_vaccinations:total_boosters, 
                ~replace_na(., 0))) %>%
  rename(country = country.x) %>%
  select(-country.y)

c19_daily

Data Terkini

Input:

Ouput:

c19_latest <- c19_daily %>%
  # get latest data
  group_by(iso_code, country) %>%
  mutate(latest_date = max(last_update)) %>%
  ungroup() %>%
  filter(latest_date == last_update) %>%
  # get first_event
  left_join(
  c19_daily %>%
    pivot_longer(c(total_confirmed, total_death, total_vaccinations),
                   names_to = "grp",
                   values_to = "total") %>%
    filter(total > 0) %>%
    group_by(iso_code, grp) %>%
    summarise(last_update = min(last_update)) %>%
    ungroup() %>%
    pivot_wider(names_from = grp, values_from = last_update) %>%
    rename(first_confirmed = total_confirmed,
           first_death = total_death,
           first_vaccination = total_vaccinations)) %>%
  # vaccine manufacture
  left_join(vaccine_manufacture, by = "iso_code") %>%
  select(iso_code:last_update, 
         first_confirmed, first_death, 
         total_confirmed:total_active,
         first_vaccination,
         total_vaccinations:total_boosters,
         vaccines_manufacture = vaccines)

c19_latest